// Table 7 - Margin Debt and The Bust
** obtain the formal margin level at the peak 6/12/2015

use ../src/daily_margin.dta, clear
keep if date<=mdy(6,12,2015) & date>=mdy(1,1,2015)
gsort stkcd -date 
by stkcd: keep if _n==1
gen peak_formal_margin_cap = margin_hold/float_cap 
gen code = stkcd

keep code peak_formal_margin_cap margin_hold float_cap
gen ln_margin_hold = ln(1+margin_hold)
preserve 

* shadow margin data 
use ../src/sm_master_d.dta, clear
keep if date<=mdy(6,12,2015) & date>=mdy(6,1,2015)
collapse (mean) sm_balance, by(code)
tempfile temp_sm_balance
save `temp_sm_balance', replace

restore

merge 1:1 code using `temp_sm_balance'
drop if _m==2
replace sm_balance=0 if _m==1
drop _merge 
// Scale up To Account for Unobserved (x20 - We see 5% of market)
gen peak_shadow_margin_cap = 20*sm_balance/float_cap 
gen peak_total_margin_cap = peak_shadow_margin_cap + peak_formal_margin_cap
keep code peak_formal_margin_cap peak_shadow_margin_cap peak_total_margin_cap
tempfile temp_sm_scale
save `temp_sm_scale', replace // temp2


* stock characteristics 

use ../src/monthly_stock_chars, clear
keep if date2<=ym(2015,5)
gsort code -date2 
by code: keep if _n==1
tempfile chars
save `chars', replace //temp


* Crash return between 6/15 to 11/27, 2015

use ../dta/stock_daily.dta, clear

keep if date>=mdy(6,15,2015) & date<=mdy(11,27,2015) /* 24 weeks */

gen ln_ret = ln(1+ret)
gen ln_ret_dgtw = ln(1+ret_dgtw)
gen ln_ret_mkt = ln(1+ret_mkt)

collapse (sum) ln_ret ln_ret_dgtw ln_ret_mkt , by(code)
gen crash_ret = ln_ret /* use log return */

merge 1:1 code using `chars' 
drop _m 

merge 1:1 code using `temp_sm_scale' 
drop _m 


merge 1:1 code using "../src/0th_shsz.dta"
gen plot_a = 0 
replace plot_a = 1 if _merge == 3 
drop _merge 

merge 1:1 code using "../src/1st_shsz.dta"
gen plot_b = 0 
replace plot_b = 1 if _merge == 3 & plot_a == 0
drop _merge 

merge 1:1 code using "../src/2nd_shsz.dta"
gen vintage1 = 0 
replace vintage1 = 1 if _merge == 3 & plot_b == 0
drop _merge

merge 1:1 code using "../src/3rd_shsz.dta"
gen vintage2 = 0 
replace vintage2 = 1 if _merge == 3 & vintage1 == 0 
drop _merge 

merge 1:1 code using "../src/4th_shsz.dta"
gen vintage3 = 0
replace vintage3 = 1 if _merge == 3 & vintage2 == 0 
gen marginable = 0 
replace marginable = 1 if _merge == 3 
drop _merge 


*** Results:  

foreach x in  peak_total_margin_cap{
	egen d_`x' = xtile(`x'), nq(5)
	gen hi_`x' = 0
	replace hi_`x' = 1 if d_`x'==5
}

* Panel B 
eststo clear
eststo: reg crash_ret  peak_total_margin_cap     ln_cap bm ret_12 turnover_float ret, robust 
gen x=e(sample)
eststo: reg crash_ret  hi_peak_total_margin_cap  ln_cap bm ret_12 turnover_float ret if e(sample), robust 

esttab using ../esttab/tab7_b.tex, keep(hi_peak_* peak_total_margin_cap) b(3) se(3)  star(* 0.10 ** 0.05 *** 0.01) nogaps no nonotes replace  frag ///
coeflabel(hi_peak_total_margin_cap "Top Quintile" peak_total_margin_cap "Peak Total Margin" ) noobs 
eststo clear



* Panel A
tabstat  crash_ret  peak_total_margin_cap if x==1, stat(mean p50 sd )
tabstat  crash_ret  peak_total_margin_cap if x==1, stat(mean p50 sd) by(hi_peak_total_margin_cap )
// Outputting
estpost tabstat crash_ret peak_total_margin_cap if x==1, ///
    statistics(mean p50 sd) by(hi_peak_total_margin_cap)
eststo panelA
	


esttab panelA ///
    using "../esttab/Tab7_a.tex", replace style(tex) ///
    unstack                       /// arrange 'by()' categories side-by-side
    noobs nonumber               /// omit row labels and the regression number
    label                         /// use variable labels instead of variable names
    cells(crash_ret peak_total_margin_cap) ///
    mtitles("All" "By hi_peak_total_margin_cap") ///
    title(Table 7 - Panel A: Summary Statistics)
	





